"""
Case Type   : mysql protocol compatibility
Case Name   : 协议兼容下验证日期处理函数1
Create At   : 2024/10/23
Owner       : lvlintao666
Description :
    1、设置协议兼容相关配置
    2、验证curdate()函数
    3、验证current_time()函数
    4、curtime()函数
    5、current_timestamp()函数
    6、localtime()函数
    7、localtimestamp()函数
    8、now()函数
    9、sysdate()函数
    10、date()函数
    11、day()函数
    12、清理环境
Expect      :
    1、配置成功
    2、成功
    3、成功
    4、成功
    5、成功
    6、成功
    7、成功
    8、成功
    9、成功
    10、成功
    11、成功
    12、清理成功
History     :
"""

import os
import time
import unittest

from yat.test import Node
from yat.test import macro

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.CommonMySQL import CommonMySQL
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()


class ProtoTestCase(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.com = Common()
        self.comsh = CommonSH('PrimaryDbUser')
        self.pri_node = Node('PrimaryDbUser')
        self.my_node = Node('mysql5')
        self.db_name = "proto_test_db"
        self.conf_path = os.path.join(macro.DB_INSTANCE_PATH,
                                      'postgresql.conf')
        self.create_db = f"drop database if exists {self.db_name};" \
                         f"create database {self.db_name} dbcompatibility='B'"
        self.user = f"proto_test_46"
        self.password = f"proto_test123"
        self.create_u = f"drop user if exists {self.user} cascade;" \
                        f"create user {self.user} with password '{self.password}';" \
                        f"grant all privileges to {self.user};" \
                        f"select set_native_password('{self.user}','{self.password}','');"
        self.show_cmd = f"show enable_dolphin_proto;" \
                        f"show dolphin_server_port;"
        self.curdate = f"select curdate();"
        self.last_day = f"select last_day('2015-06-08');" \
                        f"select last_day('2015-06-08')+1;"
        self.current_time = f"select current_time();" \
                            f"select current_time()+1;" \
                            f"select current_time(3);"
        self.curtime = f"select curtime();" \
                       f"select curtime()+1;" \
                       f"select curtime(6);"
        self.current_timestamp = f"select current_timestamp;" \
                                 f"select current_timestamp();" \
                                 f"select current_timestamp()+1; " \
                                 f"select current_timestamp(5);"
        self.localtime = f"select localtime;" \
                         f"select localtime(); " \
                         f"select localtime()+1;" \
                         f"select localtime(5);"
        self.localtimestamp = f"select localtimestamp;" \
                              f"select localtimestamp(); " \
                              f"select localtimestamp()+1;" \
                              f"select localtimestamp(5);"
        self.now = f"select now(); " \
                   f"select now()+1;" \
                   f"select now(5);"
        self.sysdate = f"select sysdate(); " \
                       f"select sysdate()+1;" \
                       f"select sysdate(5);"
        self.date = f"select date('2020-01-01');" \
                    f"select date('2020-01-01 11:11:11'); "
        self.day = f"select day('2021-05-08'); " \
                   f"select day('2024-12-31');"
        self.clean_tb = f"drop user if exists {self.user} cascade;"
        self.clean_db = f"drop database {self.db_name};"

    def test_index(self):
        text = '--step1:设置协议兼容相关配置;expect:创建成功--'
        logger.info(text)
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       'enable_dolphin_proto = on')
        logger.info(msg)
        my_port = int(self.pri_node.db_port) + 10
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       f'dolphin_server_port = {my_port}')
        logger.info(msg)
        sed_cmd = f"sed -i '$a dolphin.default_database_name={self.db_name}' " \
                  f"{self.conf_path}"
        logger.info(sed_cmd)
        res = self.com.get_sh_result(self.pri_node, sed_cmd)
        logger.info(res)

        msg = self.comsh.restart_db_cluster()
        logger.info(msg)
        show_res = self.comsh.execut_db_sql(self.show_cmd)
        logger.info(show_res)
        self.assertIn('on', show_res, '执行失败' + text)
        self.assertIn(f'{my_port}', show_res, '执行失败' + text)

        logger.info(self.create_db)
        db_res = self.comsh.execut_db_sql(self.create_db)
        logger.info(db_res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, db_res,
                      '执行失败' + text)
        logger.info(self.create_u)
        u_res = self.comsh.execut_db_sql(self.create_u, dbname=self.db_name)
        logger.info(u_res)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, u_res,
                      '执行失败' + text)

        text = '--step2:M*连接数据库curdate()函数;expect:创建成功--'
        logger.info(text)
        my_sh = CommonMySQL('mysql5')
        res = my_sh.execut_db_sql(sql=self.curdate, dbname=self.db_name, host=self.pri_node.db_host,
                                  port=my_port, user=self.user, password=self.password)
        logger.info(res)
        self.assertNotIn('ERROR', res, '执行失败' + text)

        text = '--step3:M*连接数据库current_time()函数;expect:创建成功--'
        logger.info(text)
        res = my_sh.execut_db_sql(sql=self.current_time, dbname=self.db_name, host=self.pri_node.db_host,
                                  port=my_port, user=self.user, password=self.password)
        logger.info(res)
        self.assertNotIn('ERROR', res, '执行失败' + text)

        text = '--step4:M*连接数据库curtime()函数;expect:成功--'
        logger.info(text)
        res = my_sh.execut_db_sql(sql=self.curtime, dbname=self.db_name, host=self.pri_node.db_host,
                                  port=my_port, user=self.user, password=self.password)
        logger.info(res)
        self.assertNotIn('ERROR', res, '执行失败' + text)

        text = '--step5:M*连接数据库current_timestamp()函数;expect:成功--'
        logger.info(text)
        res = my_sh.execut_db_sql(sql=self.current_timestamp, dbname=self.db_name, host=self.pri_node.db_host,
                                  port=my_port, user=self.user, password=self.password)
        logger.info(res)
        self.assertNotIn('ERROR', res, '执行失败' + text)

        text = '--step6:M*连接数据库localtime()函数;expect:成功--'
        logger.info(text)
        res = my_sh.execut_db_sql(sql=self.localtime, dbname=self.db_name, host=self.pri_node.db_host,
                                  port=my_port, user=self.user, password=self.password)
        logger.info(res)
        self.assertNotIn('ERROR', res, '执行失败' + text)

        text = '--step7:M*连接数据库localtimestamp()函数;expect:成功--'
        logger.info(text)
        res = my_sh.execut_db_sql(sql=self.localtimestamp, dbname=self.db_name, host=self.pri_node.db_host,
                                  port=my_port, user=self.user, password=self.password)
        logger.info(res)
        self.assertNotIn('ERROR', res, '执行失败' + text)

        text = '--step8:M*连接数据库now()函数;expect:成功--'
        logger.info(text)
        res = my_sh.execut_db_sql(sql=self.now, dbname=self.db_name, host=self.pri_node.db_host,
                                  port=my_port, user=self.user, password=self.password)
        logger.info(res)
        self.assertNotIn('ERROR', res, '执行失败' + text)

        text = '--step9:M*连接数据库sysdate()函数;expect:成功--'
        logger.info(text)
        res = my_sh.execut_db_sql(sql=self.sysdate, dbname=self.db_name, host=self.pri_node.db_host,
                                  port=my_port, user=self.user, password=self.password)
        logger.info(res)
        self.assertNotIn('ERROR', res, '执行失败' + text)

        text = '--step10:M*连接数据库date()函数;expect:成功--'
        logger.info(text)
        res = my_sh.execut_db_sql(sql=self.date, dbname=self.db_name, host=self.pri_node.db_host,
                                  port=my_port, user=self.user, password=self.password)
        logger.info(res)
        self.assertNotIn('ERROR', res, '执行失败' + text)

        text = '--step11:M*连接数据库day()函数;expect:成功--'
        logger.info(text)
        res = my_sh.execut_db_sql(sql=self.day, dbname=self.db_name, host=self.pri_node.db_host,
                                  port=my_port, user=self.user, password=self.password)
        logger.info(res)
        self.assertNotIn('ERROR', res, '执行失败' + text)

    def tearDown(self):
        text = '--step12:清理环境;expect:成功--'
        logger.info(text)
        clean_res = self.comsh.execut_db_sql(self.clean_tb, dbname=self.db_name)
        logger.info(clean_res)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG, clean_res,
                      '执行失败' + text)
        clean_res = self.comsh.execut_db_sql(self.clean_db)
        logger.info(clean_res)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, clean_res,
                      '执行失败' + text)
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       'enable_dolphin_proto = off')
        logger.info(msg)
        self.assertTrue(msg, '执行失败' + text)
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       f'dolphin_server_port = 3308')
        logger.info(msg)
        sed_cmd = f"sed -i '/dolphin.default_database_name={self.db_name}/d' " \
                  f"{self.conf_path}"
        logger.info(sed_cmd)
        res = self.com.get_sh_result(self.pri_node, sed_cmd)
        logger.info(res)
        msg = self.comsh.restart_db_cluster()
        logger.info(msg)
        status = self.comsh.get_db_cluster_status()
        logger.info(status)
        self.assertTrue("Degraded" in status or "Normal" in status)
        logger.info(f'-----{os.path.basename(__file__)} end-----')
